﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

using Nd.Webs.UP.Group.BBS.Entity;
using Nd.Webs.ClassLib;

namespace Nd.Webs.UP.Group.BBS.DataAccess
{
    /// <summary>
    /// 数据库操作基础类
    /// </summary>
    public class DaCommon : IDisposable
    {

        #region 私有成员
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        //private static string _CONNECTIONSTRING = @"user id=km;password=kminea;server=EAServer\MSSQL2005;database=KM;Connect Timeout=1200;";//CryptoHelper.DES_Decrypt(ConfigurationManager.AppSettings["SQLConnString2005"], "ndwebweb");
        private static string _CONNECTIONSTRING = CryptoHelper.DES_Decrypt(ConfigurationManager.AppSettings["SQLConnString2005"], "ndwebweb");
        private static string _ATTACHMENTPATH = @"D:\Attachment";//CryptoHelper.DES_Decrypt(ConfigurationManager.AppSettings["AttachmentPath"], "ndwebweb");
        #endregion

        #region 圈子论坛数据库连接字符串
        /// <summary>
        /// 圈子论坛数据库连接字符串
        /// 数据来自AppSettings["SQLConnString2005"]
        /// </summary>
        public static string ConnectionStr
        {
            get
            {
                return _CONNECTIONSTRING;
                //return CryptoHelper.DES_Decrypt(ConfigurationManager.AppSettings["SQLConnString2005"], "ndwebweb");
            }
        }

        /// <summary>
        /// 圈子论坛附件存放路径
        /// 数据来自AppSettings["AttachmentPath"]
        /// </summary>
        public static string AttachmentPath
        {
            get
            {
                return _ATTACHMENTPATH;
            }
        }
        #endregion

        #region 查询语句拼装
        
        #region 查询,修改,删除,添加语句构建
        /// <summary>
        /// 获取查询,删除,更新整表及插入记录的语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="opt">操作类型[Select , Insert, Update, Delete]</param>
        /// <returns>得到语句</returns>
        public static string QueryStringMaker(string tableName,Dictionary<string,string> columns,DatebaseOperate opt)
        {
            return QueryStringMaker(tableName, columns,new Dictionary<string, string>(), String.Empty, opt);
        }
        /// <summary>
        /// 根据条件获取查询,删除,更新的语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">条件</param>
        /// <param name="opt">操作类型[Select , Update, Delete]</param>
        /// <returns>得到语句</returns>
        public static string QueryStringMaker(string tableName,Dictionary<string,string> columns, Dictionary<string, string> filter, DatebaseOperate opt)
        {
            return QueryStringMaker(tableName, columns,filter, String.Empty, opt);
        }

        /// <summary>
        /// 根据条件获取查询,添加的语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="others">其他条件[Select语句为Group或Order子句,Insert语句为Select子句的From部分(不含From)]</param>
        /// <param name="opt">操作类型[Select,Insert]</param>
        /// <returns>生成的语句</returns>
        public static string QueryStringMaker(string tableName, Dictionary<string,string> columns,string others, DatebaseOperate opt)
        {
            return QueryStringMaker(tableName, columns,new Dictionary<string, string>(), others, opt);
        }

        /// <summary>
        /// 根据条件生成查询,添加,修改,删除的语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">条件</param>
        /// <param name="others">其他条件[Select语句为Group或Order子句,Insert语句为Select子句的From部分(不含From)]</param>
        /// <param name="opt">操作类型[Select , Insert, Update, Delete]</param>
        /// <returns>生成的语句</returns>
        public static string QueryStringMaker(string tableName, Dictionary<string,string> columns,Dictionary<string, string> filter, string others, DatebaseOperate opt)
        {
            
            //查询语句
            if (opt == DatebaseOperate.Select)
                return SelectStringMaker(tableName, columns, filter, others);
            //更新语句
            else if (opt == DatebaseOperate.Update)
                return UpdateStringMaker(tableName, columns, filter);
            //添加语句
            else if (opt == DatebaseOperate.Insert)
                return InsertStringMaker(tableName, columns, others);
            //删除语句
            else if (opt == DatebaseOperate.Delete)
                return DeleteStringMaker(tableName, filter);
            else
                return String.Empty;
        }

        /// <summary>
        /// 将Select语句中列名,从String格式转换成Dictionary (该方法只能用于构建Select语句)
        /// </summary>
        /// <param name="strColumn">Select语句要查询的列</param>
        /// <returns>列的Dictionary格式</returns>
        public static Dictionary<string, string> SelectColumnMaker(string strColumn)
        {
            Dictionary<string, string> columns = new Dictionary<string, string>();
            string[] cols = strColumn.Split('|');

            for (int i = 0; i < cols.Length; i++)
                columns.Add(cols[i], cols[i]);
            return columns;
        }
        #endregion

        #region 分页的查询语句构建
        /// <summary>
        /// 构建分页的查询语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">条件</param>
        /// <param name="others">其他条件</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNumber">页号</param>
        /// <param name="PK">分页依据字段</param>
        /// <returns>生成的语句</returns>
        public static string QueryStringMaker(string tableName, Dictionary<string,string> columns,Dictionary<string, string> filter, string others, int pageSize, int pageNumber, string PK)
        {
            return PagingStringMaker(tableName, columns, filter, others, pageSize, pageNumber, PK);
        }

        /// <summary>
        /// 构建分页的查询语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">条件</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNumber">页号</param>
        /// <param name="PK">分页依据字段</param>
        /// <returns>生成的语句</returns>
        public static string QueryStringMaker(string tableName,Dictionary<string,string> columns, string others, int pageSize, int pageNumber, string PK)
        {
            return PagingStringMaker(tableName, columns, new Dictionary<string,string>(), others, pageSize, pageNumber, PK);
        }

        /// <summary>
        /// 构建分页的查询语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">列名</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNumber">页号</param>
        /// <param name="PK">分页依据字段</param>
        /// <returns>生成的语句</returns>
        public static string QueryStringMaker(string tableName,Dictionary<string,string> columns,int pageSize, int pageNumber, string PK)
        {
            return PagingStringMaker(tableName, columns, new Dictionary<string, string>(), String.Empty, pageSize, pageNumber, PK);
        }
        #endregion

        #region 构建各种不同的查询语句
        /// <summary>
        /// 获取SELECT语句
        /// </summary>
        /// <param name="tableName">查询的表明</param>
        /// <param name="columns">查询的列</param>
        /// <param name="filter">Filter</param>
        /// <param name="others">其他条件,例如Group,Order等</param>
        /// <returns></returns>
        private static string SelectStringMaker(string tableName, Dictionary<string,string> columns, Dictionary<string, string> filter,string others)
        {
            if(columns.Count == 0) return String.Empty;

            string strSqlCommand = String.Empty;
            string strColumn = String.Empty;
            if (columns.Count > 0)
            {
                foreach (KeyValuePair<string, string> pair in columns)
                    strColumn += (pair.Key + ",");
                strColumn = strColumn.TrimEnd(new char[] { ',' });
            }

            strSqlCommand = "Select " + strColumn + " From " + tableName;
            if (filter.Count == 0)
                return strSqlCommand;

            strSqlCommand += " Where ";
            foreach (KeyValuePair<string, string> pair in filter)
                strSqlCommand += (" (" + pair.Key + pair.Value + ") And");
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { 'A', 'n', 'd' });

            if (others != String.Empty)
                strSqlCommand += (" " + others);

            return strSqlCommand;
        }
        /// <summary>
        /// 获取SELECT语句
        /// </summary>
        /// <param name="tableName">查询的表明</param>
        /// <param name="columns">查询的列</param>
        /// <param name="filter">Filter</param>
        /// <param name="others">其他条件,例如Group,Order等</param>
        /// <param name="p">参数集</param>
        /// <returns></returns>
        private static string SelectStringMaker(string tableName, Dictionary<string, string> columns, Dictionary<string, string> filter, string others,ref SqlParameter[] p)
        {
            if (columns.Count == 0) return String.Empty;

            //初始化参数
            if (p == null)
                p = new SqlParameter[filter.Count];

            int count = 0;
            string strSqlCommand = String.Empty;
            string strColumn = String.Empty;
            if (columns.Count > 0)
            {
                foreach (KeyValuePair<string, string> pair in columns)
                    strColumn += (pair.Key + ",");
                strColumn = strColumn.TrimEnd(new char[] { ',' });
            }

            strSqlCommand = "Select " + strColumn + " From " + tableName;
            if (filter.Count == 0)
                return strSqlCommand;

            strSqlCommand += " Where ";
            foreach (KeyValuePair<string, string> pair in filter)
            {
                strSqlCommand += (" (" + pair.Key + "=@" + pair.Key + ") And");
                p[count] = new SqlParameter("@" + pair.Key, pair.Value);
                count++;
            }
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { 'A', 'n', 'd' });

            if (others != String.Empty)
                strSqlCommand += (" " + others);

            return strSqlCommand;
        }

        /// <summary>
        /// 组合更新语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="modifyitems">修改项</param>
        /// <param name="filter">修改条件</param>
        /// <returns>生成的语句</returns>
        private static string UpdateStringMaker(string tableName, Dictionary<string, string> modifyitems, Dictionary<string, string> filter)
        {
            string strSqlCommand = String.Empty;
            if (modifyitems.Count == 0) return strSqlCommand;

            strSqlCommand = "Update " + tableName + " Set ";
            //组合更新项
            foreach (KeyValuePair<string, string> pair in modifyitems)
                strSqlCommand += (pair.Key + "='" + pair.Value + "',");
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { ',' });

            if (filter.Count == 0) return strSqlCommand;

            strSqlCommand += " Where ";
            foreach (KeyValuePair<string, string> pair in filter)
                strSqlCommand += (" (" + pair.Key + pair.Value + ") And");
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { 'A', 'n', 'd' });
            return strSqlCommand;
        }

        /// <summary>
        /// 参数化的更新语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="modifyitems">要修改的项</param>
        /// <param name="filter">条件</param>
        /// <returns>参数化的查询字符串</returns>
        private static string UpdateStringMaker(string tableName, Dictionary<string, string> modifyitems, Dictionary<string, string> filter,ref SqlParameter[] p)
        {
            //初始化参数
            if (p == null)
                p = new SqlParameter[modifyitems.Count + filter.Count];

            string strSqlCommand = String.Empty;
            if (modifyitems.Count == 0) return strSqlCommand;

            strSqlCommand = "Update " + tableName + " Set ";
            int count = 0;
            //组合更新项
            foreach (KeyValuePair<string, string> pair in modifyitems)
            {
                strSqlCommand += (pair.Key + "=@" + pair.Key + ",");
                p[count] = new SqlParameter("@" + pair.Key, pair.Value);
                count++;
            }
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { ',' });

            //组合条件
            if (filter.Count == 0) return strSqlCommand;
            strSqlCommand += " Where ";
            
            foreach (KeyValuePair<string, string> pair in filter)
            {
                strSqlCommand += (" (" + pair.Key + "=@f" + pair.Key + ") And");
                p[count] = new SqlParameter("@f" + pair.Key, pair.Value);
                count++;
            }
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { 'A', 'n', 'd' });
            return strSqlCommand;
        }

        /// <summary>
        /// 构建添加语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertitems">要插入的数据 Key=字段名 Value=字段值</param>
        /// <param name="others">当使用Insert的Select子句时,表示Select的From子句部分,不使用时为String.Empty</param>
        /// <returns>组成结果</returns>
        private static string InsertStringMaker(string tableName, Dictionary<string, string> insertitems,string others)
        {
            string strSqlCommand = String.Empty;
            if (insertitems.Count == 0) return strSqlCommand;

            if (others == String.Empty)
                strSqlCommand = "Insert Into " + tableName + "({0}) Values ({1})";
            else
                strSqlCommand = "Insert Into " + tableName + "({0}) Select {1} From " + others;

            string columns = String.Empty;
            string values = String.Empty;

            foreach (KeyValuePair<string, string> pair in insertitems)
            {
                columns += (pair.Key + ",");
                values += (pair.Value + ",");
            }
            columns = columns.TrimEnd(new char[] { ',' });
            values = values.TrimEnd(new char[] { ',' });

            return String.Format(strSqlCommand, columns, values);
        }
        /// <summary>
        /// 构建添加语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="insertitems">要插入的数据 Key=字段名 Value=字段值</param>
        /// <param name="p">参数集合</param>
        /// <returns>组成结果</returns>
        private static string InsertStringMaker(string tableName, Dictionary<string, string> insertitems,ref SqlParameter[] p)
        {
            if (p == null)
                p = new SqlParameter[insertitems.Count];

            string strSqlCommand = String.Empty;
            if (insertitems.Count == 0) return strSqlCommand;
            strSqlCommand = "Insert Into " + tableName + "({0}) Values ({1})";

            string columns = String.Empty;
            string paras = String.Empty;
            int count = 0;

            foreach (KeyValuePair<string, string> pair in insertitems)
            {
                columns += (pair.Key + ",");
                paras += ("@" + pair.Key + ",");
                p[count] = new SqlParameter("@" + pair.Key , pair.Value);
                count++;
            }
            columns = columns.TrimEnd(new char[] { ',' });
            paras = paras.TrimEnd(new char[] { ',' });

            return String.Format(strSqlCommand, columns, paras);
        }

        /// <summary>
        /// 构建删除语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filter">删除条件集合</param>
        /// <returns>构建的语句</returns>
        private static string DeleteStringMaker(string tableName, Dictionary<string, string> filter)
        {
            string strSqlCommand = String.Empty;
            strSqlCommand = "Delete " + tableName;
            if (filter.Count == 0)
                return strSqlCommand;

            strSqlCommand += " Where ";
            foreach (KeyValuePair<string, string> pair in filter)
                strSqlCommand += (" (" + pair.Key + pair.Value + ") And");
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { 'A', 'n', 'd' });
            return strSqlCommand;
        }

        /// <summary>
        /// 构建参数化的删除语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="filter">删除的条件</param>
        /// <returns>参数化的删除语句</returns>
        private static string DeleteStringMaker(string tableName, Dictionary<string, string> filter, ref SqlParameter[] p)
        {
            if (p == null)
                p = new SqlParameter[filter.Count];

            int count = 0;
            string strSqlCommand = String.Empty;
            strSqlCommand = "Delete " + tableName;
            if (filter.Count == 0)
                return strSqlCommand;

            strSqlCommand += " Where ";
            foreach (KeyValuePair<string, string> pair in filter)
            {
                strSqlCommand += (" (" + pair.Key + "=@" + pair.Key + ") And");
                p[count] = new SqlParameter("@" + pair.Key, pair.Value);
                count++;
            }
            strSqlCommand = strSqlCommand.TrimEnd(new char[] { 'A', 'n', 'd' });
            return strSqlCommand;
        }

        /// <summary>
        /// 分页语句的构建
        /// </summary>
        /// <param name="tableName">表名或视图名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">筛选器</param>
        /// <param name="others">其他条件</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNumber">页号</param>
        /// <param name="PK">主键</param>
        /// <returns>查询语句</returns>
        private static string PagingStringMaker(string tableName, Dictionary<string, string> columns, Dictionary<string, string> filter, string others, int pageSize, int pageNumber, string PK)
        {
            string strSqlCommand = String.Empty;
            strSqlCommand = SelectStringMaker(tableName, columns, filter, String.Empty);
            if (strSqlCommand == String.Empty)
                return strSqlCommand;
            //替换Select子句
            strSqlCommand = strSqlCommand.Replace("Select", "Select Top " + pageSize.ToString());
            //添加分页用子句
            strSqlCommand += " And " + PK + " Not In (Select Top " + pageSize * (pageNumber - 1) + " " + PK + " From " + tableName + ")";

            if (others != String.Empty)
                strSqlCommand += (" " + others);
            return strSqlCommand;
        }
        /// <summary>
        /// 分页语句的构建
        /// </summary>
        /// <param name="tableName">表名或视图名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">筛选器</param>
        /// <param name="others">其他条件</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNumber">页号</param>
        /// <param name="PK">主键</param>
        /// <returns>查询语句</returns>
        private static string PagingStringMaker(string tableName, Dictionary<string, string> columns, Dictionary<string, string> filter, string others, int pageSize, int pageNumber, string PK,bool counting)
        {
            string strSqlCommand = String.Empty;
            strSqlCommand = SelectStringMaker(tableName, columns, filter, String.Empty);
            if (strSqlCommand == String.Empty)
                return strSqlCommand;
            //替换Select子句
            strSqlCommand = strSqlCommand.Replace("Select", "Select Top " + pageSize.ToString());
            if (counting)
                strSqlCommand = strSqlCommand.Insert(strSqlCommand.IndexOf(tableName) + tableName.Length + 1, strSqlCommand.Substring(strSqlCommand.IndexOf("Where")));
            //添加分页用子句
            strSqlCommand += " And " + PK + " Not In (Select Top " + pageSize * (pageNumber - 1) + " " + PK + " From " + tableName + " " + strSqlCommand.Substring(strSqlCommand.LastIndexOf("Where")) + others + ")";

            if (others != String.Empty)
                strSqlCommand += (" " + others);
            return strSqlCommand;
        }
        #endregion

        #endregion

        #region 数据库操作的通用虚方法
        /// <summary>
        /// 分页的通用方法
        /// </summary>
        /// <param name="tableName">表名或视图名</param>
        /// <param name="columns">列名</param>
        /// <param name="filter">筛选器</param>
        /// <param name="others">其他条件</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="pageNumber">页号</param>
        /// <param name="PK">主键</param>
        /// <param name="t">类型</param>
        /// <returns>查询结果集合</returns>
        protected virtual List<object> Paging(string columns, Dictionary<string, string> filter, string others, string tablename, int pageSize, int pageNumber, string PK, Type t,bool counting)
        {
            string strExecuteCommand = String.Empty;
            Dictionary<string,string> column = SelectColumnMaker(columns);
            strExecuteCommand = PagingStringMaker(tablename, column, filter, others, pageSize, pageNumber, PK,counting);
            if (strExecuteCommand == String.Empty) return null;

            DataTable table = null;
            table = SqlHelper.ExecuteDataset(DaCommon.ConnectionStr, CommandType.Text, strExecuteCommand).Tables[0];
            if (table == null) return null;
            List<object> objs = TableToObject(table, t);
            return objs;
        }
        /// <summary>
        /// 查询单表的通用方法
        /// </summary>
        /// <param name="columns">查询的字段</param>
        /// <param name="filter">查询条件</param>
        /// <param name="tablename">表名</param>
        /// <param name="t">集合的类型</param>
        /// <returns>结果集合</returns>
        private List<object> Query(string columns, Dictionary<string, string> filter, string tablename,Type t)
        {
            return Query(columns, filter, String.Empty, tablename, t);
        }

        /// <summary>
        /// 查询单表的通用方法
        /// </summary>
        /// <param name="columns">查询的字段</param>
        /// <param name="filter">查询条件</param>
        /// <param name="other">其他条件</param>
        /// <param name="tablename">表名</param>
        /// <param name="t">集合的类型</param>
        /// <returns>结果集合</returns>
        private List<object> Query(string columns, Dictionary<string, string> filter, string other, string tablename, Type t)
        {
            string strSqlExecute = String.Empty;
            Dictionary<string, string> column = new Dictionary<string, string>();
            column = DaCommon.SelectColumnMaker(columns);
            strSqlExecute = DaCommon.QueryStringMaker(tablename, column, filter, other, DatebaseOperate.Select);

            DataTable table = null;
            if (strSqlExecute == String.Empty) return null;
            table = SqlHelper.ExecuteDataset(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute).Tables[0];

            List<object> objs = TableToObject(table, t);
            return objs;
        }

        /// <summary>
        /// 查询单表的通用方法(取全部数据)
        /// </summary>
        /// <param name="columns">查询的字段</param>
        /// <param name="tablename">表名</param>
        /// <param name="t">集合的类型</param>
        /// <returns>结果集合</returns>
        protected virtual List<object> Query(string columns, string tablename, Type t)
        {
            return this.Query(columns, new Dictionary<string, string>(), tablename,t);
        }

        /// <summary>
        /// 检查符合条件的记录是否存在 
        /// </summary>
        /// <param name="columns">查询列</param>
        /// <param name="filter">查询条件</param>
        /// <param name="tablename">表名</param>
        /// <returns>执行结果</returns>
        protected virtual OperateResult Query(string columns, Dictionary<string, string> filter, string tablename)
        {
            SqlParameter[] p = null;
            string strSqlExecute = String.Empty;
            Dictionary<string, string> column = new Dictionary<string, string>();
            column = DaCommon.SelectColumnMaker(columns);
            strSqlExecute = DaCommon.SelectStringMaker(tablename, column, filter, String.Empty, ref p);

            if (strSqlExecute == String.Empty) return OperateResult.ItemExsist;
            object o = SqlHelper.ExecuteScalar(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute, p);

            if (o == null)
                return OperateResult.ItemNotExsist;
            return OperateResult.ItemExsist;
        }

        /// <summary>
        /// 进行数据查询
        /// </summary>
        /// <param name="columns">列</param>
        /// <param name="filter">查询条件</param>
        /// <param name="tablename">表名</param>
        /// <param name="t">集合类型</param>
        /// <param name="useparam">是否使用参数</param>
        /// <returns>查询结果</returns>
        protected virtual List<object> Query(string columns, Dictionary<string, string> filter, string tablename, Type t, bool useparam)
        {
            return Query(columns, filter, String.Empty, tablename, t, useparam);
        }

        /// <summary>
        /// 进行数据查询
        /// </summary>
        /// <param name="columns">列</param>
        /// <param name="filter">查询条件</param>
        /// <param name="others">其他条件 如Order By等</param>
        /// <param name="tablename">表名</param>
        /// <param name="t">集合类型</param>
        /// <param name="useparam">是否使用参数</param>
        /// <returns>查询结果</returns>
        protected virtual List<object> Query(string columns, Dictionary<string, string> filter, string others, string tablename, Type t, bool useparam)
        {
            if (!useparam)
                return this.Query(columns, filter, others, tablename, t);

            SqlParameter[] p = null;
            string strSqlExecute = String.Empty;
            Dictionary<string, string> column = new Dictionary<string, string>();
            column = DaCommon.SelectColumnMaker(columns);
            strSqlExecute = DaCommon.SelectStringMaker(tablename, column, filter, others, ref p);

            DataTable table = null;
            if (strSqlExecute == String.Empty) return null;
            table = SqlHelper.ExecuteDataset(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute, p).Tables[0];

            List<object> objs = TableToObject(table, t);
            return objs;
        }
        /// <summary>
        /// 单表的插入的通用方法
        /// </summary>
        /// <param name="columns">插入的项</param>
        /// <param name="other">其他选项</param>
        /// <param name="tablename">表名</param>
        /// <returns>操作结果</returns>
        protected virtual OperateResult Insert(Dictionary<string, string> columns, string other, string tablename)
        {
            if (columns.Count == 0) return OperateResult.ItemNotExsist;
            if (tablename == String.Empty || tablename == "") return OperateResult.Failure;
            string strSqlExecute = String.Empty;

            strSqlExecute = DaCommon.QueryStringMaker(tablename, columns, String.Empty, DatebaseOperate.Insert);
            //执行
            try
            {
                if (SqlHelper.ExecuteNonQuery(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute) != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }
        /// <summary>
        /// 使用参数方式插入数据
        /// </summary>
        /// <param name="columns">插入的列</param>
        /// <param name="tablename">表名</param>
        /// <returns>操作结果</returns>
        protected virtual OperateResult Insert(Dictionary<string, string> columns, string tablename)
        {
            if (columns.Count == 0) return OperateResult.ItemNotExsist;
            if (tablename == String.Empty || tablename == "") return OperateResult.Failure;
            string strSqlExecute = String.Empty;

            SqlParameter[] p = null;
            strSqlExecute = InsertStringMaker(tablename, columns,ref p);
            if (p == null) return OperateResult.Failure;

            //执行
            try
            {
                if (SqlHelper.ExecuteNonQuery(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute,p) != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }
        /// <summary>
        /// 单表更新的通用方法(采用原始的拼接方式)
        /// </summary>
        /// <param name="modifyitems">修改的项</param>
        /// <param name="filter">修改条件</param>
        /// <param name="tablename">修改的表名</param>
        /// <returns>操作结果</returns>
        protected virtual OperateResult Modify(Dictionary<string, string> modifyitems, Dictionary<string, string> filter, string tablename)
        {
            string strSqlExecute = String.Empty;
            if (modifyitems.Count == 0) return OperateResult.Failure;
            if (filter.Count == 0) return OperateResult.Failure;
            strSqlExecute = QueryStringMaker(tablename, modifyitems, filter, DatebaseOperate.Update);
            //执行
            try
            {
                if (SqlHelper.ExecuteNonQuery(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute) != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

        /// <summary>
        /// 使用参数方式
        /// </summary>
        /// <param name="modifyitems">修改的项</param>
        /// <param name="filter">修改条件</param>
        /// <param name="tablename">修改的表名</param>
        /// <returns>操作结果</returns>
        protected virtual OperateResult Modify(Dictionary<string, string> modifyitems, Dictionary<string, string> filter, string tablename, bool nouse)
        {
            string strSqlExecute = String.Empty;
            if (modifyitems.Count == 0) return OperateResult.Failure;
            if (filter.Count == 0) return OperateResult.Failure;

            SqlParameter[] p = null;
            strSqlExecute = UpdateStringMaker(tablename, modifyitems, filter,ref p);
            //执行
            try
            {
                if (SqlHelper.ExecuteNonQuery(DaCommon.ConnectionStr, CommandType.Text, strSqlExecute,p) != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

        /// <summary>
        /// 单表删除的通用方法
        /// </summary>
        /// <param name="filter">执行条件</param>
        /// <param name="tablename">表名</param>
        /// <returns>执行结果</returns>
        protected virtual OperateResult Delete(Dictionary<string, string> filter, string tablename)
        {
            string strSqlExecute = String.Empty;
            //构建删除语句
            strSqlExecute = QueryStringMaker(tablename, new Dictionary<string, string>(), filter, DatebaseOperate.Delete);

            //执行
            try
            {
                if (SqlHelper.ExecuteNonQuery(ConnectionStr, CommandType.Text, strSqlExecute) != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

        /// <summary>
        /// 执行参数化的删除语句
        /// </summary>
        /// <param name="filter">条件</param>
        /// <param name="tablename">表名</param>
        /// <param name="nouse">标示</param>
        /// <returns>操作结果</returns>
        protected virtual OperateResult Delete(Dictionary<string, string> filter, string tablename, bool nouse)
        {
            string strSqlExecute = String.Empty;
            //构建删除语句
            SqlParameter[] p = null;
            strSqlExecute = DeleteStringMaker(tablename, filter, ref p);

            //执行
            try
            {
                if (SqlHelper.ExecuteNonQuery(ConnectionStr, CommandType.Text, strSqlExecute,p) != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spname">存储过程名</param>
        /// <param name="lastReturn">是否有返回</param>
        /// <param name="sqlParams">存储过程的参数</param>
        /// <returns>执行结果</returns>
        protected virtual OperateResult ExecuteStoreProcedure(string spname, bool lastReturn, params SqlParameter[] sqlParams)
        {
            if (spname == String.Empty) throw new ArgumentNullException("spname", "DA:sp名称不能为空");

            try
            {
                int ret = -1;
                ret = SqlHelper.ExecuteNonQuery(ConnectionStr, CommandType.StoredProcedure, spname, sqlParams);

                if (lastReturn)
                {
                    if (Convert.ToInt32(sqlParams[sqlParams.Length - 1].Value) == 0)
                        return OperateResult.Success;
                    else if (Convert.ToInt32(sqlParams[sqlParams.Length - 1].Value) == -1)
                        return OperateResult.ExecptionOccured;
                    else
                        return OperateResult.Failure;
                }
                if(ret != -1)
                    return OperateResult.Success;
                return OperateResult.Failure;
            }
            catch (SqlException sex)
            {
                throw new ApplicationException(sex.Message);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message);
            }
        }

        /// <summary>
        /// 执行多个SQL语句,在事务中执行
        /// </summary>
        /// <param name="multisql">多个SQL语句组成的列表</param>
        /// <returns>执行结果</returns>
        protected virtual OperateResult ExecuteMultiSQL(List<string> multisql)
        {
            if (multisql.Count == 0) return OperateResult.ItemNotExsist;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = DaCommon.ConnectionStr;
            connection.Open();
            SqlTransaction trans = connection.BeginTransaction();

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandTimeout = connection.ConnectionTimeout;
            command.Transaction = trans;

            try
            {
                foreach (string str in multisql)
                {
                    command.CommandText = str;
                    command.ExecuteNonQuery();
                }
                trans.Commit();
                return OperateResult.Success;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
                command.Dispose();
                connection.Dispose();
            }
        }

        /// <summary>
        /// 执行多条查询语句,使用参数的方式,如果执行的语句中存在不需要参数的语句,请将参数表设置为null;
        /// </summary>
        /// <param name="multisql">需要执行的SQL语句和参数对,其中SQL语句不能重复</param>
        /// <returns>最终执行的结果</returns>
        protected virtual OperateResult ExecuteMultiSQL(Dictionary<string, SqlParameter[]> multisql)
        {
            if (multisql.Count == 0) return OperateResult.ItemNotExsist;

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = DaCommon.ConnectionStr;
            connection.Open();
            SqlTransaction trans = connection.BeginTransaction();

            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandTimeout = connection.ConnectionTimeout;
            command.Transaction = trans;

            try
            {
                foreach (KeyValuePair<string, SqlParameter[]> sqlpair in multisql)
                {
                    command.CommandText = sqlpair.Key;
                    if (sqlpair.Value != null)
                    {
                        for (int i = 0; i < sqlpair.Value.Length; i++)
                            command.Parameters.Add(sqlpair.Value[i]);
                    }
                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                }
                trans.Commit();
                return OperateResult.Success;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                connection.Close();
                command.Dispose();
                connection.Dispose();
            }
        }
        #endregion 

        #region 将Datatable转换成对象集合的通用虚方法
        /// <summary>
        /// 将Datatable转换成对象集合
        /// </summary>
        /// <param name="table">要转换的DataTable</param>
        /// <param name="t">集合元素的类型</param>
        /// <returns>集合体</returns>
        protected virtual List<object> TableToObject(DataTable table, Type t)
        {
            List<object> objs = new List<object>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                object o = Activator.CreateInstance(t);
                foreach (DataColumn column in table.Columns)
                {
                    if (!(table.Rows[i][column] is DBNull))
                    {
                        PropertyInfo info = t.GetProperty(column.ColumnName);
                        info.SetValue(o, table.Rows[i][column], null);
                    }
                }
                objs.Add(o);
            }
            return objs;
        }
        
        /// <summary>
        /// 列名转换的方法
        /// </summary>
        /// <param name="columnname">原始列名</param>
        /// <returns>转换后的列名</returns>
        protected delegate string ColumnNameConvert(string columnname);
        /// <summary>
        /// 将Datatable转换成对象集合
        /// </summary>
        /// <param name="table">要转换的DataTable</param>
        /// <param name="colConvert">列名转换的方法</param>
        /// <param name="t">集合元素的类型</param>
        /// <returns>集合体</returns>
        protected virtual List<object> TableToObject(DataTable table, ColumnNameConvert colConvert, Type t)
        {
            List<object> objs = new List<object>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                object o = Activator.CreateInstance(t);
                foreach (DataColumn column in table.Columns)
                {
                    if (!(table.Rows[i][column] is DBNull))
                    {
                        PropertyInfo info = t.GetProperty(colConvert(column.ColumnName));
                        info.SetValue(o, table.Rows[i][column], null);
                    }
                }
                objs.Add(o);
            }
            return objs;
        }

        /// <summary>
        /// 将DataRead中的数据转换成对象集合 
        /// </summary>
        /// <param name="reader">DataReader</param>
        /// <param name="t">类型</param>
        /// <returns>类型集合</returns>
        protected virtual IList<object> ReaderToObject(IDataReader reader, Type t)
        {
            IList<object> objs = new List<object>();
            if (reader == null) return null;
            if (reader.IsClosed) return null;

            while (reader.Read())
            {
                object o = Activator.CreateInstance(t);
                PropertyInfo[] infos = t.GetProperties();
                foreach (PropertyInfo info in infos)
                    info.SetValue(o, reader[info.Name], null);
                objs.Add(o);
            }
            reader.Close();
            return objs;
        }

        /// <summary>
        /// 将DataRead中的数据转换成对象集合 
        /// </summary>
        /// <param name="reader">DataReader</param>
        /// <param name="colConvert">列名和属性名转换的方法</param>
        /// <param name="t">类型</param>
        /// <returns>类型集合</returns>
        protected virtual IList<object> ReadToObject(IDataReader reader, ColumnNameConvert colConvert, Type t)
        {
            IList<object> objs = new List<object>();
            if (reader == null) return null;
            if (reader.IsClosed) return null;

            while (reader.Read())
            {
                object o = Activator.CreateInstance(t);
                PropertyInfo[] infos = t.GetProperties();
                foreach (PropertyInfo info in infos)
                    info.SetValue(o, reader[colConvert(info.Name)], null);
                objs.Add(o);
            }
            reader.Close();
            return objs;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            return;
        }

        #endregion
    }
}
